*********************************************************************************
** Replication code
** Authors: Meilin Ma, Fei Qin, Jayson Lusk
** Title: Retailer assortment under Demand Shock
** Date: 05/26/2024
** Table 1 and 2: Summary statistics for key dependent variables/income and wealth variables
*********************************************************************************

******************
** Monthly
******************

clear all

** compute dependent variables in 2006 at the store level
use "c_3603_mnnm.dta", clear

rename type* typ*

keep if year==2006
collapse (mean)pkstr_mnnm flvstr_mnnm stlstr_mnnm typstr_mnnm prdstr_mnnm strvat_ui  upstr_mn strvat_mnnm avgupcsz_mn, by(store_code_uc parent_code retailer_code store_zip3 state county dma_code)
rename *_mnnm *_2006 
rename upstr_mn upstr_mn_2006
rename strvat_ui strvat_ui_2006
rename avgupcsz_mn avgupcsz_mn_2006

save mn2006.dta, replace


clear all

** add 2006 store level size info to income/wealth info
use "c_3603_mnnm.dta", clear

merge m:1 store_code_uc parent_code retailer_code store_zip3 state county dma_code using mn2006.dta
drop _merge

********************************************************************************
** baseline
** 2007-2010
********************************************************************************

** label var
label variable unemprate "unemployment rate"
label variable lnhv_ad_r "log house value"
label variable lnavg_wage_r "log wage rate"
label variable lnmedian_inc_r "log median inc"
label variable cmp_cntnm "#competitor stores"
label variable pop_ad "county pop"
label variable upcstr_2006 "2006 avg #UPC"
label variable brdstr_2006 "2006 avg #brd"
label variable pkstr_2006 "2006 avg #pkg"
label variable flvstr_2006 "2006 avg #flv"
label variable stlstr_2006 "2006 avg #sty"
label variable typstr_2006 "2006 avg #typ"
label variable prdstr_2006 "2006 avg #prd"
label variable upc_all "mkt #UPC"
label variable brd_all "mkt #brd"
label variable pkstr_mnnm "#packages"
label variable flvstr_mnnm "#flavors"
label variable stlstr_mnnm "#styles"
label variable typestr_mnnm "#types"
label variable prdstr_mnnm "#prod types"
label variable strvat_ui "unq index"
label variable strvat_mnnm "#varieties"

** scale var
qui replace pop_ad=pop_ad/100
qui replace cmp_cntnm=cmp_cntnm/100
gen emp = 100 - unemprate
label var emp "employment rate"
gen lgp = log(upstr_mn)
gen lgp_2006 = log(upstr_mn_2006)

** rescale uniqueness index
qui replace strvat_ui=strvat_ui*100
qui replace strvat_ui_2006 = strvat_ui_2006*100


** merge with SNAP data
gen mn_snap = 1 if mn < 7
replace mn_snap = 7 if mn >= 7

**
merge m:1 state county mn_snap year using "snap_yogurt.dta"
drop if _merge == 2
drop _merge

**
gen lg_snap_ppl = log(snap_ppl)
gen lg_snap_hh = log(snap_hh)
gen lg_snap_dl = log(snap_dl)

qui replace snap_dl = snap_dl/1000000

*****************************************************************
** Summary
*****************************************************************

sum emp lnhv_ad_r snap_dl  if lg_snap_dl !=. & upcstr_2006 !=. & upc_all != . & cmp_cntnm !=.  & trend !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & (channel == 3 | channel == 5)

**
sum upcstr_mnnm avgupcsz_mn if emp != . & lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=. & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & trend !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==3

sum upcstr_mnnm  avgupcsz_mn if emp != . & lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=. & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & trend !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==5

**
sum  strvat_mnnm strvat_ui  if emp != . & lnhv_ad_r !=. & lg_snap_dl !=. & strvat_2006 !=. & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & trend !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==3

sum strvat_mnnm strvat_ui if emp != . & lnhv_ad_r !=. & lg_snap_dl !=. & strvat_2006 !=. & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & trend !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==5

******************
** Quarterly
******************

clear all

** compute dependent variables in 2006 at the store level
use "c_3603_qtnm.dta", clear

keep if year==2006
collapse (mean)pkstr_qtnm strvat_qtnm strvat_ui upstr_qt avgupcsz_qt , by(store_code_uc parent_code retailer_code store_zip3 state county dma_code)
rename *_qtnm *_2006 
rename avgupcsz_qt avgupcsz_2006
rename upstr_qt upstr_qt_2006
rename strvat_ui strvat_ui_2006

save qt2006.dta, replace


clear all

** add 2006 store level size info to income/wealth info
use "c_3603_qtnm.dta", clear

merge m:1 store_code_uc parent_code retailer_code store_zip3 state county dma_code using qt2006.dta
drop _merge

********************************************************************************
** baseline
** 2007-2010
********************************************************************************

** label var
label variable unemprate "unemployment rate"
label variable lnhv_ad_r "log house value"
label variable lnavg_wage_r "log wage rate"
label variable lnmedian_inc_r "log median inc"
label variable cmp_cntnm "#competitor stores"
label variable pop_ad "county pop"
label variable upcstr_2006 "2006 avg #UPC"
label variable brdstr_2006 "2006 avg #brd"
label variable pkstr_2006 "2006 avg #pkg"
label variable upc_all "mkt #UPC"
label variable brd_all "mkt #brd"
label variable pkstr_qtnm "#packages"
label variable flvstr_qtnm "#flavors"
label variable stlstr_qtnm "#styles"
label variable typestr_qtnm "#types"
label variable prdstr_qtnm "#prod types"
label variable strvat_ui "unq index"
label variable strvat_qtnm "#varieties"

** scale var
qui replace pop_ad=pop_ad/100
qui replace cmp_cntnm=cmp_cntnm/100
gen emp = 100 - unemprate
label var emp "employment rate"
gen lgp = log(upstr_qt)
gen lgp_2006 = log(upstr_qt_2006)

qui replace strvat_ui=strvat_ui*100
qui replace strvat_ui_2006 = strvat_ui_2006*100


** merge SNAP data
gen qt_snap = 1 if qt == 1 | qt == 2
replace qt_snap = 2 if qt == 3 | qt == 4


merge m:1 state county qt_snap year using "snap_yogurt.dta"
drop if _merge == 2
drop _merge

gen lg_snap_ppl = log(snap_ppl)
gen lg_snap_hh = log(snap_hh)
gen lg_snap_dl = log(snap_dl)

qui replace snap_dl = snap_dl/1000000


*****************************************************************
** Summary
*****************************************************************

sum avg_wage_r snap_dl if lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & (channel==3 | channel ==5)

**
sum upcstr_qtnm avgupcsz_qt if lnavg_wage_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==3

sum upcstr_qtnm avgupcsz_qt if lnavg_wage_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==5

**
sum strvat_qtnm strvat_ui if lnavg_wage_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & strvat_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==3

sum strvat_qtnm strvat_ui if lnavg_wage_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & strvat_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & qt !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==5


******************
** Yearly
******************

clear all

** compute dependent variables in 2006 at the store level
use "c_3603_yrnm.dta", clear

rename type* typ*

keep if year==2006
collapse (mean)pkstr_yrnm strvat_yrnm strvat_ui upstr_yr avgupcsz_yr, by(store_code_uc parent_code retailer_code store_zip3 state county dma_code)
rename *_yrnm *_2006 
rename upstr_yr upstr_yr_2006
rename strvat_ui strvat_ui_2006
rename avgupcsz_yr avgupcsz_2006

save yr2006.dta, replace


clear all

** add 2006 store level size info to income/wealth info
use "c_3603_yrnm.dta", clear

merge m:1 store_code_uc parent_code retailer_code store_zip3 state county dma_code using yr2006.dta
drop _merge

********************************************************************************
** baseline
** 2007-2010
********************************************************************************

** label var
label variable unemprate "unemployment rate"
label variable lnhv_ad_r "log house value"
label variable lnavg_wage_r "log wage rate"
label variable lnmedian_inc_r "log median inc"
label variable cmp_cntnm "#competitor stores"
label variable pop_ad "county pop"
label variable upcstr_2006 "2006 avg #UPC"
label variable brdstr_2006 "2006 avg #brd"
label variable pkstr_2006 "2006 avg #pkg"
label variable upc_all "mkt #UPC"
label variable brd_all "mkt #brd"
label variable pkstr_yrnm "#packages"
label variable flvstr_yrnm "#flavors"
label variable stlstr_yrnm "#styles"
label variable typestr_yrnm "#types"
label variable prdstr_yrnm "#prod types"
label variable strvat_ui "unq index"
label variable strvat_yrnm "#varieties"

** scale var
qui replace pop_ad=pop_ad/100
qui replace cmp_cntnm=cmp_cntnm/100
gen emp = 100 - unemprate
label var emp "employment rate"
gen lgp = log(upstr_yr)
gen lgp_2006 = log(upstr_yr_2006)

qui replace strvat_ui=strvat_ui*100
qui replace strvat_ui_2006 = strvat_ui_2006*100


** merge SNAP data
merge m:1 state county year using "snap_yogurt_yr.dta"
drop if _merge == 2
drop _merge

gen lg_snap_ppl = log(snap_ppl_yr)
gen lg_snap_hh = log(snap_hh_yr)
gen lg_snap_dl = log(snap_dl_yr)

qui replace snap_dl = snap_dl/1000000


*****************************************************************
** Summary
*****************************************************************

sum median_inc_r pop_ad  sex_ratio snap_dl if lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=.  & upc_all != . & cmp_cntnm !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & (channel==3 | channel == 5)

**
sum upcstr_yrnm avgupcsz_yr if lnmedian_inc_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==3

sum upcstr_yrnm avgupcsz_yr if lnmedian_inc_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & upcstr_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==5

**
sum  strvat_yrnm strvat_ui  if lnmedian_inc_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & strvat_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==3

sum  strvat_yrnm strvat_ui  if lnmedian_inc_r != . & lnhv_ad_r !=. & lg_snap_dl !=. & strvat_2006 !=.  & upc_all != . & cmp_cntnm !=. & pop_ad != . & sex_ratio !=. & year !=. & retailer_code !=. & countyfipscode !=. & year < 2011 & year> 2006 & channel==5
